# 做一个学生点名系统，系统里支持：存入学生、更改学生、删除学生、点到学生姓名方法
#
#  存入学生（姓名、年龄、家庭地址、手机号）： 存入成功后，提示xxx存入成功
# 更改学生（姓名、年龄、家庭地址、手机号）：可以修改学生内容的任意一个字段
#  删除学生：删除的学生将不在展示
# 展示全部学生：可以看到全部的学生姓名
#  学生姓名：随机抽取任意一个存在的学生信息，然后打印：到！。举例： 刘闯到！
#
# 以上内容，需要连接数据库，每次的数据都需要增、删、改、查去做
#
# student表：name、age、address、phone_number
from faker import Faker
import pymysql
import random
class PyMysql:
    def __init__(self, user, password, host, database):
        self.user = user
        self.password = password
        self.host = host
        self.database = database
    def connect_mysql(self):
        self.db = pymysql.connect(user=self.user, password=self.password, host=self.host, database=self.database,
                                  cursorclass=pymysql.cursors.DictCursor)
        self.cursor = self.db.cursor()  # 游标
    def disconnect(self):
        self.cursor.close()
        self.db.close()
    def __edit(self,sql):
        try:
            self.cursor.execute(sql)
            self.db.commit() # 提交
            result = True
        except:
            print("sql失败")
            result = False
        return result
    def select_one(self,sql):
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchone()
        except:
            print("查询数据库错误！")
            result = False
        return result
    def select_all(self,sql):
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()

        except:
            print("查询数据库错误！")
            result = False
        return result
    def insert(self,sql):
        self.__edit(sql)
    def delete(self,sql):
        self.__edit(sql)
    def update(self,sql):
        self.__edit(sql)

class Student_System(PyMysql):
    def __init__(self,user, password, host, database):
        PyMysql.__init__(self,user, password, host, database)
        self.faker = Faker(locale='zh_cn')
    def insert_student(self): # name、age、address、phone_number
        # self.faker.name() # 名字
        # self.faker.address() # 地址
        # self.faker.phone_number() #手机号
        # age = random.randint(19,24) # 年龄
        self.connect_mysql()
        self.insert(f"insert into student(name,age,address,phone_number) values('{self.faker.name()}','{str(random.randint(19,24))}','{self.faker.address()}','{self.faker.phone_number()}')")
        self.disconnect()
    def update_student(self,name,new_name,new_address,new_phone,new_age): # 姓名、年龄、家庭地址、手机号
        self.connect_mysql()
        result = self.select_all(f"select * from student where name='{name}'")
        for i in result:
            if i['name'] == name:
                self.update(f"update student set name='{new_name}',address='{new_address}',phone_number='{new_phone}',age='{new_age}' where name='{name}'")
            else:
                print("学生不存在，修改失败！")
        self.disconnect()

    def delete_student(self,name):
        self.connect_mysql()
        result = self.select_all(f"select * from student where name='{name}'")
        for i in result:
            if i['name'] == name:
                self.delete(f'delete from student where name="{name}"')
        self.disconnect()

    def show_student(self):
        self.connect_mysql()
        result = self.select_all('select * from student')
        self.disconnect()
        return result


s1 = Student_System(host = "101.200.84.192",user = 'root',password='123456',database='wangchangshuai')
# s1.insert_student()

# s1.update_student("王长帅","王长帅不是很帅的长帅","沈阳市","13504785596","49")

s1.delete_student('王长帅不是很帅的长帅')
print(s1.show_student())
